﻿


CREATE PROCEDURE [dbo].[exbug_ListaCheltuieliSanitareDepartamente]
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@IDJurnal int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT CASE WHEN d.IDTipDepartament In (16,18) THEN 0
	WHEN d.IDTipDepartament In (2) THEN 1 
	ELSE 2 END ord, d.Nume,td.Nume TipDepartament,
	sum(e.Valoare) Chelt_totale, 
	CASE WHEN Sum(d.NumericField)<>0 THEN Sum(e.Valoare)/Sum(d.NumericField) ELSE 0 END CostMediuPat,
	CASE WHEN Sum(ecp.Internati)<>0 THEN Sum(e.Valoare)/Sum(ecp.Internati) ELSE 0 END CostMediuPacient,
	CASE WHEN Sum(ecp.ZileSpitalizare)<>0 THEN Sum(e.Valoare)/Sum(ecp.ZileSpitalizare) ELSE 0 END CostMediuZiSpital,
	Coalesce(Sum(d.NumericField),0) Nr_paturi,
	Coalesce(Sum(ecp.Internati),0) Nr_internati,
	Coalesce(Sum(ecp.ZileSpitalizare),0) Nr_zile_spitalizare
	FROM Departamente d JOIN TipDepartament td ON td.IDTipDepartament = d.IDTipDepartament
	JOIN ExecutieCheltuieli e ON e.IDDepartament = d.IDDepartament AND d.Activ=1 and e.CodUnitate = @CodUnitate
	JOIN 
	(SELECT e.IDDepartament,e.IDJurnal,e.CodUnitate,Sum(Internati) Internati,Sum(ZileSpitalizare) ZileSpitalizare,Data FROM 
	  ExecutieCheltuieliPacienti e JOIN CaseAsigurariSanatate cas ON e.IDCasaAsigSanatate = cas.IDCasaAsigSanatate 
	  --AND cas.CasaImplicita=1
	 WHERE e.CodUnitate = @CodUnitate
	 GROUP BY e.IDDepartament,e.IDJurnal,e.CodUnitate,data
	) ecp ON e.IDDepartament = ecp.IDDepartament and ecp.CodUnitate=e.CodUnitate and e.Data=ecp.Data
	WHERE d.CodUnitate = @CodUnitate AND e.Data>=@DeLaData and e.Data <=@PanaLaData
	AND e.ArtAl in ('20.04.02')
	GROUP BY d.IDTipDepartament,td.Nume,d.Nume
	having SUM(Valoare)<>0 
END